﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'P_InsertTreeBasicReferenceIDs')
	BEGIN
		DROP  Procedure  esf_sso.P_InsertTreeBasicReferenceIDs
	END

GO

CREATE Procedure esf_sso.P_InsertTreeBasicReferenceIDs
(
	@pParentID		INT
	,@pChildID		INT
	,@pID			INT	OUT
	,@pValue		VARCHAR (MAX) = NULL
)
AS

	SET @pID = NULL


	print ' * pParentID = ' + CONVERT (VARCHAR (MAX), @pParentID)

	IF @pChildID IS NOT NULL
	BEGIN
		
		print ' * pChild NO es nulo'
		
		SELECT @pID = ID FROM esf_sso.TreeBasicReference 
			WHERE 
				ParentBasicReferenceID = @pParentID
				AND BasicReferenceID = @pChildID

		print ' * Obtenido pID = ' + CONVERT (VARCHAR (MAX), @pID)

	END
	ELSE
	BEGIN

		print '* pChild es nulo'

		SELECT @pID = ID FROM esf_sso.TreeBasicReference 
			WHERE 
				ParentBasicReferenceID = @pParentID
				AND BasicReferenceID IS NULL
				
		print ' * Obtenido pID = ' + CONVERT (VARCHAR (MAX), @pID)

	END


	IF @pID IS NULL
	BEGIN
		
		print 'pID es nulo'
		
		INSERT INTO esf_sso.TreeBasicReference (ParentBasicReferenceID, BasicReferenceID, Value)
		SELECT @pParentID, @pChildID, @pValue

		SET @pID = @@IDENTITY
		
		print ' * Obtenido pID = ' + CONVERT (VARCHAR (MAX), @pID)

	END
	ELSE
	BEGIN

		print 'HAY pID = ' + CONVERT (VARCHAR (MAX), @pID)
		
		UPDATE 
			esf_sso.TreeBasicReference
		SET 
			Value = @pValue
		WHERE
			ID = @pID

	END
	

GO 